<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> 
<html>
<head>
<title>MySQL PHP tutorial</title>
<link rel="stylesheet" href="/cfg/format.css" type="text/css">
<meta http-equiv="content-type" content="text/html; charset=utf-8">
<meta name="keywords" content="PHP, MySQL, language, database, tutorial, learn MySQL PHP">
<meta name="description" content="This is MySQL PHP tutorial. In this tutorial, you will
learn the basics of database programming in MySQL and PHP language.">
<meta name="language" content="en">
<meta name="author" content="Jan Bodnar">
<meta name="distribution" content="global">

<script type="text/javascript" src="/lib/jquery.js"></script>
<script type="text/javascript" src="/lib/common.js"></script>

<script type="text/javascript">
  (function() {
    var po = document.createElement('script'); po.type = 'text/javascript'; po.async = true;
    po.src = 'https://apis.google.com/js/plusone.js';
    var s = document.getElementsByTagName('script')[0]; s.parentNode.insertBefore(po, s);
  })();
</script>

<script type="text/javascript">

  var _gaq = _gaq || [];
  _gaq.push(['_setAccount', 'UA-5536206-1']);
  _gaq.push(['_trackPageview']);

  (function() {
    var ga = document.createElement('script'); ga.type = 'text/javascript'; ga.async = true;
    ga.src = ('https:' == document.location.protocol ? 'https://ssl' : 'http://www') + '.google-analytics.com/ga.js';
    var s = document.getElementsByTagName('script')[0]; s.parentNode.insertBefore(ga, s);
  })();

</script>

</head>

<body>

<div class="container">

<div id="wide_ad" class="ltow">
<script type="text/javascript"><!--
google_ad_client = "pub-9706709751191532";
/* 160x600, August 2011 */
google_ad_slot = "2484182563";
google_ad_width = 160;
google_ad_height = 600;
//-->
</script>
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
</div>

<div class="content">


<a href="/" title="Home">Home</a>


<h1>MySQL PHP tutorial</h1>

<p>
This is a PHP programming tutorial for the MySQL database. It covers the 
basics of MySQL programming with PHP. It uses the <b>generic mysql</b> module. 
The examples were created and tested on Ubuntu Linux. There is a similar
<a href="/tutorials/mysqlcapitutorial/">MySQL C API tutorial</a>,
<a href="/databases/mysqlpythontutorial/">MySQL Python tutorial</a> and 
<a href="/db/postgresqlphp/">PostgreSQL PHP tutorial</a> on ZetCode.
</p>

<p>
If you need to refresh your knowledge of the PHP language, there is a full 
<a href="/lang/php/">PHP tutorial</a> on ZetCode. 
</p>


<h2>About MySQL database</h2>

<p>
MySQL is a leading open source database management system. It is a multi user, 
multithreaded database management system. MySQL is especially popular on the web. 
It is one of the parts of the very popular <b>LAMP</b>
platform. Linux, Apache, MySQL, PHP. Currently MySQL is owned by Oracle.
MySQL database is available on most important OS
platforms. It runs under BSD Unix, Linux, Windows or Mac.
Wikipedia and YouTube use MySQL. These sites manage millions of queries
each day. MySQL comes in two versions. MySQL server system and MySQL
embedded system.
</p>

<div class="center">
<script type="text/javascript"><!--
google_ad_client = "ca-pub-9706709751191532";
/* top_horizontal */
google_ad_slot = "3327173442";
google_ad_width = 468;
google_ad_height = 60;
//-->
</script>
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
</div>


<h2>Before we start</h2>

<p>
We need to install several packages to execute the examples in this tutorial.
php5-cli, php5-mysql, mysql-server, mysql-client. 
</p>

<p>
The <b>php5-cli</b> is the command line interpreter for the PHP5 programming language. 
All examples in this tutorial are created on the console. I have intentionally skipped
the web interface to make the examples simpler and focus only on PHP and MySQL. 
</p>

<p>
If you don't already have MySQL installed, we must install it. 
</p>

<pre>
$ sudo apt-get install mysql-server
</pre>

<p>
This command installs the MySQL server and various other packages. 
While installing the package, we are prompted to enter
a password for the MySQL root account.
</p>

<p>
Next, we are going to create a new database user and a new database. 
We use the mysql client.
</p>

<pre>
$ service mysql status
mysql start/running, process 1238
</pre>

<p>
We check if the MySQL server is running. If not, we need
to start the server. On Ubuntu Linux, this can be done
with the service mysql start command. 
</p>

<pre>
$ sudo service mysql start
</pre>

<p>
The above command is a common way to start MySQL if we have
installed the MySQL database from packages. 
</p>

<pre>
$ sudo -b /usr/local/mysql/bin/mysqld_safe
</pre>

<p>
The above command starts MySQL server using the MySQL server
startup script. The way how we start a MySQL server might
be different. It depends whether we have installed MySQL 
from sources or from packages and also on the Linux distro.
For further information consult 
<a href="http://zetcode.com/databases/mysqltutorial/firststeps/">MySQL first steps</a>
or your Linux distro information.
</p>


<p>
Next, we are going to create a new database user and a new database. 
We use the mysql client.
</p>

<pre>
$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 30
Server version: 5.0.67-0ubuntu6 (Ubuntu)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema | 
| mysql              | 
+--------------------+
2 rows in set (0.00 sec)
</pre>

<p>
We use the <b>mysql monitor</b> client application to connect to the 
server. 
We connect to the database using the root account. We show all available
databases with the SHOW DATABASES statement. 
</p>

<pre>
mysql> CREATE DATABASE mydb;
Query OK, 1 row affected (0.02 sec)
</pre>

<p>
We create a new <b>mydb</b> database. We will use this database throughout 
the tutorial.
</p>

<pre>
mysql> CREATE USER user12@localhost IDENTIFIED BY '34klq*';
Query OK, 0 rows affected (0.00 sec)

mysql> USE mydb;
Database changed

mysql> GRANT ALL ON mydb.* to user12@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> quit;
Bye
</pre>

<p>
We create a new database user. We grant all privileges to this user
for all tables of the mydb database. 
</p>


<h2>php5-mysql</h2>

<p>
In order to connect to the MySQL database from the PHP language, we must have
php5-mysql package installed. This is a package name for Debian/Ubuntu Linux. On
other derivatives the name might differ. This package has three modules. They are
also called extensions. 
</p>

<ul>
<li>mysql module</li>
<li>mysqli module</li>
<li>pdo_mysql</li>
</ul>

<p>
The generic <b>mysql</b> module is the original PHP API for the MySQL 
database. Our tutorial covers this module. The API is procedural. 
This module does not provide all the latest features of the newer 
MySQL databases. The MySQL improved <b>mysqli</b> module is the 
recommended module for MySQL versions 4.1.3 or later. It provides both 
object oriented and procedural APIs. It has several benefits and enhancements
over the original mysql module. 
</p>

<p>
The <b>pdo_mysql</b>, PHP Data Objects module is a database abstraction 
layer for PHP applications. This module is beneficial if we write portable 
database PHP scripts. 
</p>

<h2>First script</h2>

<p>
The following script is a simple PHP script. If this small 
script runs OK, we have everything needed installed.
</p>

<pre class="code">
&lt;?php

$host = "localhost"; 
$user = "user12"; 
$pass = "34klq*"; 

$r = mysql_connect($host, $user, $pass);

if (!$r) {
    echo "Could not connect to server\n";
    trigger_error(mysql_error(), E_USER_ERROR);
} else {
    echo "Connection established\n"; 
}

echo mysql_get_server_info() . "\n"; 

mysql_close();

?&gt;
</pre>

<p>
We connect to the database and get some info about the MySQL server.
</p>

<pre class="explanation">
$host = "localhost"; 
$user = "user12"; 
$pass = "34klq*"; 
</pre>

<p>
These are three variables holding the host name, user name and password. 
The variables are needed when connecting to the MySQL database. 
</p>

<pre class="explanation">
$r = mysql_connect($host, $user, $pass);
</pre>

<p>
We use the <b>mysql_connect()</b> function to connect
to the database. The function returns a boolean value indicating
whether the connection was successfully created or not. 
The function has 3 parameters. The first is the host, where
the server is installed. The second and third parameters are the user name
and user password. 
</p>

<pre class="explanation">
if (!$r) {
    echo "Could not connect to server\n";
    trigger_error(mysql_error(), E_USER_ERROR);
} else {
    echo "Connection established\n"; 
}
</pre>

<p>
Now we check the $r variable. If it contains a boolean false, the
connection to the database was not created. We call the <b>trigger_error()</b>
function to generate an error message. The first generic message
goes to the user. The more specific error message generated with the 
trigger_error() function is logged. 
</p>

<pre class="explanation">
echo mysql_get_server_info() . "\n"; 
</pre>

<p>
The <b>mysql_get_server_info()</b> returns the MySQL server version.
</p>

<pre class="explanation">
mysql_close();
</pre>

<p>
The <b>mysql_close()</b> function closes the connection
to the database. Closing connection in our case is not necessary,
as non-persistent open links are automatically closed at the 
end of the script's execution. However, it is a good programming practice. 
</p>

<pre>
$ php version.php
5.1.41-3ubuntu12.6
5.3.2-1ubuntu4.5
</pre>

<p>
On my system, I got the following output. 
</p>

<hr class="btm">

<p>
We have a similar script. 
</p>

<pre class="code">
&lt;?php

$host = "localhost"; 
$user = "user12"; 
$pass = "34klq*"; 

$r = mysql_connect($host, $user, $pass);

if (!$r) {
    echo "Could not connect to server\n";
    trigger_error(mysql_error(), E_USER_ERROR);
} else {
    echo "Connection established\n"; 
}

$query = "SELECT VERSION()";

$rs = mysql_query($query);

if (!$rs) {
    echo "Could not execute query: $query\n";
    trigger_error(mysql_error(), E_USER_ERROR);
} else {
    echo "Query: $query executed\n"; 
}

$row = mysql_fetch_row($rs);

echo "Version: $row[0]\n";

mysql_close();

?&gt;
</pre>

<p>
We check for the version of the MySQL database. This time using
an SQL query. 
</p>

<pre class="explanation">
$query = "SELECT VERSION()";
</pre>

<p>
This is the SQL SELECT statement. It returns the version
of the database. The <b>VERSION()</b> is
a built-in MySQL function. 
</p>

<pre class="explanation">
$rs = mysql_query($query);
</pre>

<p>
The <b>mysql_query()</b> function executes an SQL
query on the database. This is a SELECT query, so the result is
a result set, containing some data. 
</p>

<pre class="explanation">
if (!$rs) {
    echo "Could not execute query: $query\n";
    trigger_error(mysql_error(), E_USER_ERROR);
} else {
    echo "Query: $query executed\n"; 
}
</pre>

<p>
In case of an error we generate an error message. Otherwise
we print the SQL query executed. 
</p>

<pre class="explanation">
$row = mysql_fetch_row($rs);
</pre>

<p>
We fetch a row from the result set. The $row variable
is an array containing data. 
</p>

<pre class="explanation">
echo "Version: $row[0]\n";
</pre>

<p>
We print the data from the array. We know from the nature of our
query, that we have only one item it the array, the MySQL version
string. 
</p>


<pre>
$ php version2.php
Connection established
Query: SELECT VERSION() executed
Version: 5.1.62-0ubuntu0.11.10.1
</pre>

<p>
Output of the script on our system. 
</p>


<h2>Creating and populating a table</h2>

<p>
Next we are going to create a database table and fill it with data.
</p>

<pre class="code">
&lt;?php

$host = "localhost"; 
$user = "user12"; 
$pass = "34klq*"; 
$db = "mydb"; 

function execute_query($query) {

    $r = mysql_query($query);

    if (!$r) {
        echo "Cannot execute query: $query\n";
        trigger_error(mysql_error()); 
    } else {
        echo "Query: $query executed\n"; 
    }
}

$r = mysql_connect($host, $user, $pass);

if (!$r) {
    echo "Could not connect to server\n";
    trigger_error(mysql_error(), E_USER_ERROR);
} else {
    echo "Connection established\n"; 
}

$r2 = mysql_select_db($db);

if (!$r2) {
    echo "Cannot select database\n";
    trigger_error(mysql_error(), E_USER_ERROR); 
} else {
    echo "Database selected\n";
}

$query = "DROP TABLE IF EXISTS Cars"; 
execute_query($query);

$query = "CREATE TABLE Cars(Id INT PRIMARY KEY, Name TEXT, 
    Price INT) ENGINE=InnoDB";
execute_query($query);

$query = "INSERT INTO Cars VALUES(1,'Audi',52642)";
execute_query($query);

$query = "INSERT INTO Cars VALUES(2,'Mercedes',57127)";
execute_query($query);

$query = "INSERT INTO Cars VALUES(3,'Skoda',9000)";
execute_query($query);

$query = "INSERT INTO Cars VALUES(4,'Volvo',29000)";
execute_query($query);

$query = "INSERT INTO Cars VALUES(5,'Bentley',350000)";
execute_query($query);

$query = "INSERT INTO Cars VALUES(6,'Citroen',21000)";
execute_query($query);

$query = "INSERT INTO Cars VALUES(7,'Hummer',41400)";
execute_query($query);

$query = "INSERT INTO Cars VALUES(8,'Volkswagen',21600)";
execute_query($query);

mysql_close();

?&gt;
</pre>

<p>
In the above code example, we create a Cars table with 8 rows.
</p>

<pre class="explanation">
function execute_query($query) {

    $r = mysql_query($query);

    if (!$r) {
        echo "Cannot execute query: $query\n";
        trigger_error(mysql_error()); 
    } else {
        echo "Query: $query executed\n"; 
    }
}
</pre>

<p>
We have created a custom execute_query() function which will be called for
each INSERT statement. 
</p>

<pre class="explanation">
$r2 = mysql_select_db($db);
</pre>

<p>
Before we can work with database tables, we must select a database.
A database is selected with a <b>mysql_select_db()</b> function.
</p>

<pre class="explanation">
if (!$r2) {
    echo "Cannot select database\n";
    trigger_error(mysql_error(), E_USER_ERROR); 
} else {
    echo "Database selected\n";
}
</pre>

<p>
Error handling for the database selection process. 
</p>

<pre class="explanation">
$query = "DROP TABLE IF EXISTS Cars"; 
execute_query($query);
</pre>

<p>
The first query drops a Cars table, if it already exists. 
</p>

<pre class="explanation">
$query = "CREATE TABLE Cars(Id INT PRIMARY KEY, Name TEXT, 
    Price INT) ENGINE=InnoDB";
execute_query($query);
</pre>

<p>
This is the SQL statement to create the Cars table. 
</p>


<pre class="explanation">
$query = "INSERT INTO Cars VALUES(1,'Audi',52642)";
execute_query($query);
</pre>

<p>
A car is inserted into the table. 
</p>

<pre class="explanation">
if (!$ok) {
   echo mysql_error();
   die("Cannot execute query. \n");
}
</pre>

<p>
In case of an error, we print the error message and
terminate the script. 
</p>

<pre>
$ php create_fill.php
Connection established
Database selected
Query: DROP TABLE IF EXISTS Cars executed
Query: CREATE TABLE Cars(Id INT PRIMARY KEY, Name TEXT, 
    Price INT) ENGINE=InnoDB executed
Query: INSERT INTO Cars VALUES(1,'Audi',52642) executed
Query: INSERT INTO Cars VALUES(2,'Mercedes',57127) executed
Query: INSERT INTO Cars VALUES(3,'Skoda',9000) executed
Query: INSERT INTO Cars VALUES(4,'Volvo',29000) executed
Query: INSERT INTO Cars VALUES(5,'Bentley',350000) executed
Query: INSERT INTO Cars VALUES(6,'Citroen',21000) executed
Query: INSERT INTO Cars VALUES(7,'Hummer',41400) executed
Query: INSERT INTO Cars VALUES(8,'Volkswagen',21600) executed
</pre>

<p>
Executing the create_fill.php script. 
</p>

<pre>
mysql> SELECT * FROM Cars;
+----+------------+--------+
| Id | Name       | Price  |
+----+------------+--------+
|  1 | Audi       |  52642 |
|  2 | Mercedes   |  57127 |
|  3 | Skoda      |   9000 |
|  4 | Volvo      |  29000 |
|  5 | Bentley    | 350000 |
|  6 | Citroen    |  21000 |
|  7 | Hummer     |  41400 |
|  8 | Volkswagen |  21600 |
+----+------------+--------+
8 rows in set (0.00 sec)
</pre>

<p>
The data inserted into the Cars table. 
</p>


<h2>Retrieving data</h2>

<p>
Now, that we have inserted some data into the database, we want to 
get it back.
</p>

<pre class="code">
&lt;?php

$host = "localhost"; 
$user = "user12"; 
$pass = "34klq*"; 
$db = "mydb";

$r = mysql_connect($host, $user, $pass);

if (!$r) {
    echo "Could not connect to server\n";
    trigger_error(mysql_error(), E_USER_ERROR);
} else {
    echo "Connection established\n"; 
}

$r2 = mysql_select_db($db);

if (!$r2) {
    echo "Cannot select database\n";
    trigger_error(mysql_error(), E_USER_ERROR); 
} else {
    echo "Database selected\n";
}

$query = "SELECT * FROM Cars LIMIT 5";

$rs = mysql_query($query);

if (!$rs) {
    echo "Could not execute query: $query";
    trigger_error(mysql_error(), E_USER_ERROR); 
} else {
    echo "Query: $query executed\n";
} 

while ($row = mysql_fetch_assoc($rs)) {
    echo $row['Id'] . " " . $row['Name'] . " " . $row['Price'] . "\n";
}

mysql_close();

?&gt;
</pre>

<p>
In this example, we retrieve five rows from the Cars table.
</p>

<pre class="explanation">
$query = "SELECT * FROM Cars LIMIT 5";
</pre>

<p>
This SQL statement selects 5 rows from the Cars table. 
</p>

<pre class="explanation">
$rs = mysql_query($query);
</pre>

<p>
We execute the query with the <b>mysql_query()</b> function 
and retrieve the result set. 
</p>

<pre class="explanation">
if (!$rs) {
    echo "Could not execute query: $query";
    trigger_error(mysql_error(), E_USER_ERROR); 
} else {
    echo "Query: $query executed\n";
} 
</pre>

<p>
If the query did not succeed, we generate an error message. 
</p>

<pre class="explanation">
while ($row = mysql_fetch_assoc($rs)) {
    echo $row['Id'] . " " . $row['Name'] . " " . $row['Price'] . "\n";
}
</pre>

<p>
We loop through the result set and print the data to the 
console. The <b>mysql_fetch_assoc()</b> function
returns an associative array of strings that corresponds to the 
fetched row, or FALSE if there are no more rows. In other words, 
the function call returns a row from the result set. This row
is in the form of an associative array. The column names
are keys to the associative array. When there are no more rows
in the result set, the function returns FALSE and the while loop
terminates.
</p>

<pre>
$ php query.php
Connection established
Database selected
Query: SELECT * FROM Cars LIMIT 5 executed
1 Audi 52642
2 Mercedes 57127
3 Skoda 9000
4 Volvo 29000
5 Bentley 350000
</pre>

<p>
This is the output of the example. 
</p>

<hr class="btm">

<p>
In the second example, we will fetch data with the 
mysql_fetch_row() function.
</p>

<pre class="code">
&lt;?php

$host = "localhost"; 
$user = "user12"; 
$pass = "34klq*"; 
$db = "mydb";

$r = mysql_connect($host, $user, $pass);

if (!$r) {
    echo "Could not connect to server\n";
    trigger_error(mysql_error(), E_USER_ERROR);
} else {
    echo "Connection established\n"; 
}

$r2 = mysql_select_db($db);

if (!$r2) {
    echo "Cannot select database\n";
    trigger_error(mysql_error(), E_USER_ERROR); 
} else {
    echo "Database selected\n";
}

$query = "SELECT Id, Name, Price From Cars LIMIT 5";

$rs = mysql_query($query);

if (!$rs) {
    echo "Could not execute query: $query";
    trigger_error(mysql_error(), E_USER_ERROR); 
} else {
    echo "Query: $query executed\n";
} 

$nrows = mysql_num_rows($rs);

for ($i = 0; $i < $nrows; $i++) {
    $row = mysql_fetch_row($rs);
    echo $row[0];
    echo " ";
    echo $row[1];
    echo " ";
    echo $row[1];
    echo "\n";
}

mysql_close();

?&gt;
</pre>

<p>
We get the first 5 rows from the Cars table.
</p>

<pre class="explanation">
$nrows = mysql_num_rows($rs);
</pre>

<p>
The <b>mysql_num_rows()</b> function gets the number of
rows from the result set.  
</p>

<pre class="explanation">
for ($i = 0; $i < $nrows; $i++) {
    $row = mysql_fetch_row($rs);
    echo $row[0];
    echo " ";
    echo $row[1];
    echo " ";
    echo $row[1];
    echo "\n";
}
</pre>

<p>
We use the for loop to iterate over the returned rows. The 
<b>mysql_fetch_row()</b> function retrieves the 
row from the result set in the form of an enumerated array.
</p>

<pre>
$ php query.php
Connection established
Query: SELECT * FROM Cars LIMIT 5 executed
1 Audi 52642
2 Mercedes 57127
3 Skoda 9000
4 Volvo 29000
5 Bentley 350000
</pre>

<p>
Output.
</p>

<hr class="btm">

<p>
In the following example, we show how to retrieve a 
specific row from a table. 
</p>

<pre class="code">
&lt;?php

$host = "localhost"; 
$user = "user12"; 
$pass = "34klq*"; 
$db = "mydb";

$r = mysql_connect($host, $user, $pass);

if (!$r) {
    echo "Could not connect to server\n";
    trigger_error(mysql_error(), E_USER_ERROR);
} else {
    echo "Connection established\n"; 
}

$r2 = mysql_select_db($db);

if (!$r2) {
    echo "Cannot select database\n";
    trigger_error(mysql_error(), E_USER_ERROR); 
} else {
    echo "Database selected\n";
}

$name = "Volkswagen";

$query = sprintf("SELECT Id, Name, Price From Cars Where Name = '%s'", 
    mysql_real_escape_string($name));

$rs = mysql_query($query);

if (!$rs) {
    echo "Could not execute query: $query\n";
    trigger_error(mysql_error(), E_USER_ERROR); 
} else {
    echo "Query: $query executed\n";
} 

while ($row = mysql_fetch_object($rs)) {
    echo $row->Id;
    echo " ";
    echo $row->Name;
    echo " ";
    echo $row->Price;
    echo "\n";
}

mysql_close();

?&gt;
</pre>

<p>
Developers must take security concerns into account when working
with input from users. We must always process the data sent from
outside world. Check for validity of the data. 
</p>

<pre class="explanation">
$name = "Volkswagen";
</pre>

<p>
In the script, we check, if we have "Volkswagen" in the Caras table.
This value might come from an xml file or a web form. We will show, how
to check it.
</p>

<pre class="explanation">
$query = sprintf("SELECT Id, Name, Price From Cars Where Name = '%s'", 
    mysql_real_escape_string($name));
</pre>

<p>
We build the SQL statement using the <b>sprintf()</b> function.
We process the $name variable with the <b>mysql_real_escape_string()</b>
function. This function escapes special characters in a string for use in an SQL statement.
This prevents SQL injection attacks and data corruption. After the variable was processed,
it is put into the SQL statement string. 
</p>

<pre class="explanation">
while ($row = mysql_fetch_object($rs)) {
    echo $row->Id;
    echo " ";
    echo $row->Name;
    echo " ";
    echo $row->Price;
    echo "\n";
}
</pre>

<p>
We fetch the data using the <b>mysql_fetch_object()</b> function.
The function fetches a result row as an object. And we use the object notation to
get the table columns. 
</p>

<pre>
$ php query3.php
Connection established
Database selected
Query: SELECT Id, Name, Price From Cars Where Name = 'Volkswagen' executed
8 Volkswagen 21600
</pre>

<p>
The output of the example. We found the car and printed
the whole row to the console. 
</p>


<h2>Escaping characters</h2>

<p>
We will have a small example demonstrating how to
escape characters. There are some characters which are
considered to be unsafe in a database environment. One
of them is a single quote character. 
</p>

<pre>
mysql> CREATE TABLE IF NOT EXISTS Authors(Id INT PRIMARY KEY AUTO_INCREMENT, 
    ->     Name VARCHAR(25)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.09 sec)
</pre>

<p>
For the example, we create an Authors table.
</p>

<pre class="code">
&lt;?php

$host = "localhost"; 
$user = "user12"; 
$pass = "34klq*"; 
$db = "mydb"; 

$r = mysql_connect($host, $user, $pass);

if (!$r) {
    echo "Could not connect to server\n";
    trigger_error(mysql_error(), E_USER_ERROR);
} else {
    echo "Connection established\n"; 
}

$r2 = mysql_select_db($db);

if (!$r2) {
    echo "Cannot select database\n";
    trigger_error(mysql_error(), E_USER_ERROR); 
} else {
    echo "Database selected\n";
}

$name = "O'Neill";
$name_es = mysql_real_escape_string($name);

$query = "INSERT INTO Authors(Name) VALUES('$name_es')";
$rs = mysql_query($query);

if (!$rs) {
    echo "Could not execute query: $query\n";
    trigger_error(mysql_error(), E_USER_ERROR); 
} else {
    echo "Query: $query executed\n";
} 

mysql_close();

?&gt;
</pre>

<p>
We insert a new author to the Authors table. The name of the author
is O'Neill. The name has an unsafe single quote character.
</p>

<pre class="explanation">
$name_es = mysql_real_escape_string($name);
</pre>

<p>
Thay is why we use the <b>mysql_real_escape_string()</b> 
function to escape this character. 
</p>

<pre class="explanation">
$query = "INSERT INTO Authors(Name) VALUES('$name_es')";
$rs = mysql_query($query);
</pre>

<p>
We create the statement and execute it.
</p>

<pre>
mysql> SELECT * FROM Authors;
+----+---------+
| Id | Name    |
+----+---------+
|  1 | O'Neill |
+----+---------+
1 row in set (0.00 sec)
</pre>

<p>
The name has been successfully written to the
table. 
</p>


<h2>Column headers</h2>

<p>
Next we will show, how to print column headers with the data
from the database table.
</p>

<pre class="code">
&lt;?php

$host = "localhost"; 
$user = "user12"; 
$pass = "34klq*"; 
$db = "mydb"; 

$r = mysql_connect($host, $user, $pass);

if (!$r) {
    echo "Could not connect to server\n";
    trigger_error(mysql_error(), E_USER_ERROR);
} else {
    echo "Connection established\n"; 
}

$r2 = mysql_select_db($db);

if (!$r2) {
    echo "Cannot select database\n";
    trigger_error(mysql_error(), E_USER_ERROR); 
} else {
    echo "Database selected\n";
}

$query = "SELECT * From Cars LIMIT 8";
    
$rs = mysql_query($query);

if (!$rs) {
    echo "Could not execute query: $query";
    trigger_error(mysql_error(), E_USER_ERROR); 
} else {
    echo "Query: $query executed\n";
} 

$cname1 = mysql_fetch_field($rs, 0);
$cname2 = mysql_fetch_field($rs, 1);
$cname3 = mysql_fetch_field($rs, 2);

printf("%3s %-11s %8s\n", $cname1->name, $cname2->name, 
    $cname3->name);

while ($row = mysql_fetch_row($rs)) {
    printf("%3s %-11s %8s\n", $row[0], $row[1], $row[2]);
}

mysql_close();

?&gt;
</pre>

<p>
Again, we print the contents of the Writers table to the
console. Now, we include the names of the columns too. 
</p>


<pre class="explanation">
$cname1 = mysql_fetch_field($rs, 0);
$cname2 = mysql_fetch_field($rs, 1);
$cname3 = mysql_fetch_field($rs, 2);
</pre>

<p>
To get a specific field name, we utilize 
the <b>mysql_fetch_field()</b> function. The function return an object
containing column information.
</p>


<pre class="explanation">
printf("%3s %-11s %8s\n", $cname1->name, $cname2->name, 
    $cname3->name);
</pre>

<p>
The column names are printed and formatted. The name property
contains the column name. 
</p>

<pre>
$ php columns.php
Connection established
Database selected
Query: SELECT * From Cars LIMIT 8 executed
 Id Name           Price
  1 Audi           52642
  2 Mercedes       57127
  3 Skoda           9000
  4 Volvo          29000
  5 Bentley       350000
  6 Citroen        21000
  7 Hummer         41400
  8 Volkswagen     21600
</pre>

<p>
Ouput of the script. 
</p>


<h2>Fields, rows</h2>

<p>
The following script counts the number of fields/columns
and rows returned by a query. 
</p>


<pre class="code">
&lt;?php

$host = "localhost"; 
$user = "user12"; 
$pass = "34klq*"; 
$db = "mydb";

$r = mysql_connect($host, $user, $pass);

if (!$r) {
    echo "Could not connect to server\n";
    trigger_error(mysql_error(), E_USER_ERROR);
} else {
    echo "Connection established\n"; 
}

$r2 = mysql_select_db($db);

if (!$r2) {
    echo "Cannot select database\n";
    trigger_error(mysql_error(), E_USER_ERROR); 
} else {
    echo "Database selected\n";
}

$query = "SELECT * FROM Cars WHERE Id IN (1, 2, 3)";

$rs = mysql_query($query);

if (!$rs) {
    echo "Could not execute query: $query\n";
    trigger_error(mysql_error(), E_USER_ERROR); 
} else {
    echo "Query: $query executed\n";
} 

echo "We have " . mysql_num_fields($rs) . " fields\n";
echo "We have " . mysql_num_rows($rs) . " rows\n";

print_r(mysql_fetch_row($rs));

mysql_close();

?&gt;
</pre>

<p>
We select three rows from the Cars table. We count the number
of rows and columns returned by a query. 
</p>

<pre class="explanation">
$query = "SELECT * FROM Cars WHERE Id IN (1, 2, 3)";
</pre>

<p>
This is the query to be executed. It selects first three rows 
from the Cars table. 
</p>

<pre class="explanation">
echo "We have " . mysql_num_fields($rs) . " fields\n";
</pre>

<p>
The <b>mysql_num_fields()</b> returns the number of fields returned
by a query. 
</p>

<pre class="explanation">
echo "We have " . mysql_num_rows($rs) . " rows\n";
</pre>

<p>
The <b>mysql_num_rows()</b> returns the number of rows returned by 
a query. 
</p>

<pre class="explanation">
print_r(mysql_fetch_row($rs));
</pre>

<p>
We print the contents of the array. 
</p>

<pre>
$ php fields_rows.php
Connection established
Database selected
Query: SELECT * FROM Cars WHERE Id IN (1, 2, 3) executed
We have 3 fields
We have 3 rows
Array
(
    [0] => 1
    [1] => Audi
    [2] => 52642
)
</pre>

<p>
Running the script. 
</p>


<h2>Writing images</h2>

<p>
Some people prefer to put their images into the database, some prefer to keep them 
on the file system for their applications. Technical difficulties arise when we 
work with millions of images. Images are binary data. MySQL database has a special 
data type to store binary data called <b>BLOB</b> (Binary Large Object). 
</p>

<pre>
mysql> CREATE TABLE Images(Id INT PRIMARY KEY AUTO_INCREMENT, Data MEDIUMBLOB);
Query OK, 0 rows affected (0.06 sec)
</pre>

<p>
For this example, we create a new table called Images. 
</p>

<pre class="code">
&lt;?php

$host = "localhost"; 
$user = "user12"; 
$pass = "34klq*"; 
$db = "mydb";

$r = mysql_connect($host, $user, $pass);

if (!$r) {
    echo "Could not connect to server\n";
    trigger_error(mysql_error(), E_USER_ERROR);
} else {
    echo "Connection established\n"; 
}

$r2 = mysql_select_db($db);

if (!$r2) {
    echo "Cannot select database\n";
    trigger_error(mysql_error(), E_USER_ERROR); 
} else {
    echo "Database selected\n";
}

$file = "woman.jpg";

$img = fopen($file, 'r');

if (!$img) {
    echo "Cannot open file for writing\n";
    trigger_error("Cannot open file for writing\n", E_USER_ERROR);
} 

$data = fread($img, filesize($file));

if (!$data) {
    echo "Cannot read image data\n";
    trigger_error("Cannot read image data\n", E_USER_ERROR);
}

$es_data = mysql_real_escape_string($data);
fclose($img);

$query = "INSERT INTO Images(Id, Data) Values(1, '$es_data')";
    
$rs = mysql_query($query);

if (!$rs) {
    echo "Could not execute query: $query";
    trigger_error(mysql_error(), E_USER_ERROR); 
} else {
    echo "Query successfully executed\n";
} 

mysql_close();

?&gt;
</pre>

<p>
In the above script, we read a jpg image and insert it 
into the Images table. 
</p>

<pre class="explanation">
$file = "woman.jpg";
</pre>

<p>
This is the image name, that we read from the filesystem and write
into the database. It is located in the same directory as the
script name. 
</p>

<pre class="explanation">
$img = fopen($file, 'r');

if (!$img) {
    echo "Cannot open file for writing\n";
    trigger_error("Cannot open file for writing\n", E_USER_ERROR);
} 

$data = fread($img, filesize($file));

if (!$data) {
    echo "Cannot read image data\n";
    trigger_error("Cannot read image data\n", E_USER_ERROR);
}
</pre>

<p>
We open and read the image. The <b>fread()</b>
function returns the data as string. 
</p>

<pre class="explanation">
$es_data = mysql_real_escape_string($data);
</pre>

<p>
We escape unsafe characters. 
</p>

<pre class="explanation">
fclose($img);
</pre>

<p>
We close the handle to the image file. 
</p>

<pre class="explanation">
$query = "INSERT INTO Images(Id, Data) Values(1, '$es_data')";
    
$rs = mysql_query($query);

if (!$rs) {
    echo "Could not execute query: $query";
    trigger_error(mysql_error(), E_USER_ERROR); 
} else {
    echo "Query successfully executed\n";
} 
</pre>

<p>
We insert the data to the newly created Images table. 
</p>


<h2>Reading images</h2>

<p>
In the previous example, we have inserted an image into the database table. 
Now we are going to read the image back from the table.
</p>

<pre class="code">
&lt;?php

$host = "localhost"; 
$user = "user12"; 
$pass = "34klq*"; 
$db = "mydb";

$r = mysql_connect($host, $user, $pass);

if (!$r) {
    echo "Could not connect to server\n";
    trigger_error(mysql_error(), E_USER_ERROR);
} else {
    echo "Connection established\n"; 
}

$r2 = mysql_select_db($db);

if (!$r2) {
    echo "Cannot select database\n";
    trigger_error(mysql_error(), E_USER_ERROR); 
} else {
    echo "Database selected\n";
}

$query = "SELECT Data FROM Images WHERE Id=1";
    
$rs = mysql_query($query);

if (!$rs) {
    echo "Could not execute query: $query";
    trigger_error(mysql_error(), E_USER_ERROR); 
} else {
    echo "Query: $query executed\n";
} 

$row = mysql_fetch_row($rs);

$file = "woman2.jpg";

$img = fopen($file, 'wb');

if (!$img) {
    echo "Cannot open file for writing\n";
    trigger_error("Cannot open file for writing\n", E_USER_ERROR);
} 

$r3 = fwrite($img, $row[0]);

if (!$r3) {
    echo "Cannot write image to file\n";
    trigger_error("Cannot write image to file\n", E_USER_ERROR);
} 

fclose($img);

mysql_close();

?&gt;
</pre>

<p>
We read one image from the Images table.
</p>


<pre class="explanation">
$query = "SELECT Data FROM Images WHERE Id=1";
</pre>

<p>
We select one record from the table. 
</p>

<pre class="explanation">
$row = mysql_fetch_row($rs);
</pre>

<p>
We fetch one row from the result set. 
There is only one row, containing the image data.
</p>

<pre class="explanation">
$file = "woman2.jpg";
</pre>

<p>
We will create a new image file name called "woman2.jpg".
</p>

<pre class="explanation">
$img = fopen($file, 'wb');

if (!$img) {
    echo "Cannot open file for writing\n";
    trigger_error("Cannot open file for writing\n", E_USER_ERROR);
} 
</pre>

<p>
We open a writable binary file. 
</p>

<pre class="explanation">
$r3 = fwrite($img, $row[0]);

if (!$r3) {
    echo "Cannot write image to file\n";
    trigger_error("Cannot write image to file\n", E_USER_ERROR);
} 
</pre>

<p>
We write the data to the filesystem using the <b>fwrite()</b>
function.
</p>

<p>
Now we should have an image called "woman2.jpg" in
our current directory. We can check if it is the same
image, that we have inserted into the table. 
</p>

<h2>Transaction support</h2>

<p>
A <b>transaction</b> is an atomic unit of database operations 
against the data in one or more databases. The effects of all the SQL statements
in a transaction can be either all committed to the database or all rolled back.
</p>

<p>
The MySQL database has different types of storage engines. The most common are the MyISAM
and the InnoDB engines. The MyISAM is the default one. There is a trade-off 
between data security and database speed. The MyISAM tables are faster to process and 
they do not support transactions. On the other hand, the InnoDB tables are more safe 
against the data loss. They support transactions. They are slower to process. 
</p>


<pre class="code">
&lt;?php

mysql_connect('localhost', 'testuser', 'test623') 
    or die("cannot connect to database\n");

mysql_select_db("testdb") or die(mysql_error());

$r1 = mysql_query("UPDATE Writers SET Name = 'Leo Tolstoy' WHERE Id = 1")
    or die(mysql_error());

$r2 = mysql_query("UPDATE Writers SET Name = 'Boris Pasternak' WHERE Id = 2")
    or die(mysql_error());

$r3 = mysql_query("UPDATE Writer SET Name = 'Leonid Leonov' WHERE Id = 3")
    or die(mysql_error());

mysql_close();

?&gt;
</pre>

<p>
In this script, we try to update three rows. The storage engine of
the table is MyISAM. 
</p>

<pre class="explanation">
$r1 = mysql_query("UPDATE Writers SET Name = 'Leo Tolstoy' WHERE Id = 1")
    or die(mysql_error());

$r2 = mysql_query("UPDATE Writers SET Name = 'Boris Pasternak' WHERE Id = 2")
    or die(mysql_error());
</pre>

<p>
Here we want to change names of authors for rows 1 and 2. 
</p>

<pre class="explanation">
$r3 = mysql_query("UPDATE Writer SET Name = 'Leonid Leonov' WHERE Id = 3")
    or die(mysql_error());
</pre>

<p>
There is an error in the SQL statement. There is no Writer table. 
</p>


<pre class="code">
$ php update.php
Table 'testdb.Writer' doesn't exist

mysql> SELECT * FROM Writers;
+----+-------------------+
| Id | Name              |
+----+-------------------+
|  1 | Leo Tolstoy       |
|  2 | Boris Pasternak   |
|  3 | Lion Feuchtwanger |
|  4 | Emile Zola        |
|  5 | Truman Capote     |
|  6 | O'Neill           |
+----+-------------------+
6 rows in set (0.00 sec)
</pre>

<p>
Running the script gives an error. But as we see, the first two rows
already were changed.
</p>

<p>
In the last example of this tutorial, we are going to recreate the 
Writers table. This time, the table will be of InnoDB type. 
InnoDB MySQL database tables support transactions. 
</p>

<pre class="code">
DROP TABLE Writers;

CREATE TABLE IF NOT EXISTS Writers(Id INT PRIMARY KEY AUTO_INCREMENT, 
    Name VARCHAR(25)) ENGINE=INNODB;

INSERT INTO Writers(Name) VALUES('Jack London');
INSERT INTO Writers(Name) VALUES('Honore de Balzac');
INSERT INTO Writers(Name) VALUES('Lion Feuchtwanger');
INSERT INTO Writers(Name) VALUES('Emile Zola');
INSERT INTO Writers(Name) VALUES('Truman Capote');
</pre>

<p>
This is writers.sql file. It is used to recreate the Writers table.
</p>


<pre>
mysql> source writers.sql
Query OK, 0 rows affected (0.03 sec)

Query OK, 0 rows affected (0.10 sec)

Query OK, 1 row affected (0.02 sec)

Query OK, 1 row affected (0.03 sec)

Query OK, 1 row affected (0.02 sec)

Query OK, 1 row affected (0.02 sec)

Query OK, 1 row affected (0.02 sec)
</pre>

<p>
We can use the <b>source</b> commnad to load and execute the sql script. 
</p>

<pre class="code">
&lt;?php

mysql_connect('localhost', 'testuser', 'test623') 
    or die("cannot connect to database\n");

mysql_select_db("testdb") or die(mysql_error());

mysql_query("SET AUTOCOMMIT=0");
mysql_query("START TRANSACTION");

$r1 = mysql_query("DELETE FROM Writers WHERE Id = 3")
    or die(mysql_error());

$r2 = mysql_query("DELETE FROM Writers WHERE Id = 4")
    or die(mysql_error());

$r3 = mysql_query("DELETE FROM Writer WHERE Id = 5")
    or die(mysql_error());

if ($r1 and $r2 and $r3) {
    mysql_query("COMMIT");
} else {
    mysql_query("ROLLBACK");
}

mysql_close();

?&gt;
</pre>

<p>
Now, we are going to execute the above script. We want to delete
three rows from the table. The third SQL statement has an error. 
</p>

<pre class="explanation">
mysql_query("START TRANSACTION");
</pre>

<p>
The <b>START TRANSACTION</b> statement starts a new transaction. All changes
must be made permanent with the <b>COMMIT</b> statement or ignored with
the <b>ROLLBACK</b> statement. 
</p>

<pre class="explanation">
if ($r1 and $r2 and $r3) {
    mysql_query("COMMIT");
} else {
    mysql_query("ROLLBACK");
}
</pre>

<p>
We commit the statements only if all SQL statements three returned True. Otherwise, we roll them 
back. In our case the $r3 variable holds False, so the statements are not made permanent
and the rows are not deleted from the table. 
</p>


<pre class="code">
$ php transaction.php
Table 'testdb.Writer' doesn't exist

mysql> SELECT * FROM Writers;
+----+-------------------+
| Id | Name              |
+----+-------------------+
|  1 | Jack London       |
|  2 | Honore de Balzac  |
|  3 | Lion Feuchtwanger |
|  4 | Emile Zola        |
|  5 | Truman Capote     |
+----+-------------------+
5 rows in set (0.00 sec)
</pre>

<p>
The error occurred before we have committed the changes to the database. 
The <b>ROLLBACK</b> statement was called and no deletions took
place. 
</p>


<div class="g-plusone"></div>

<p>
This was the MySQL PHP tutorial. You might be also interested in 
<a href="/tutorials/mysqlcapitutorial/">MySQL C API tutorial</a>, 
<a href="/databases/mysqlpythontutorial/">MySQL Python tutorial</a> or
<a href="/databases/mysqlvisualbasictutorial/">MySQL Visual Basic tutorial</a>.
</p>


<div class="center"> 
<script type="text/javascript"><!--
google_ad_client = "pub-9706709751191532";
/* horizontal */
google_ad_slot = "1734478269";
google_ad_width = 468;
google_ad_height = 60;
//-->
</script> 
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> 
</script> 
</div> 
<br>


<div class="botNav, center">
<span class="botNavItem"><a href="/">Home</a></span> ‡ <span class="botNavItem"><a href="#">Top of Page</a></span>
</div>


<div class="footer">
<div class="signature">
<a href="/">ZetCode</a> last modified May 23, 2012  <span class="copyright">&copy; 2007 - 2013 Jan Bodnar</span>
</div>
</div>

</div> <!-- content -->

</div> <!-- container -->

</body>
</html>

